﻿using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{

    public partial class VVAKDBEZ
    {

        #region
        #endregion

        public void SetUpNewRow(ref DataRow dwReturn, DataRow dwInput = null)
        {

            switch (app.company)
            {
                case "YGR":
                case "AFS":
                case "TST":
                case "UBT":
                case "SXN":
                default:
                    #region=========
                    dwReturn["VAKBMANDANT"] = app.Mandant;
                    dwReturn["VAKBKOLL"] = "     ";
                    dwReturn["VAKBART"] = "                        ";
                    dwReturn["VAKBKUNDNR"] = "00000000";
                    dwReturn["VAKBFARBE"] = "          ";
                    dwReturn["VAKBKDARTNR"] = "                             ";
                    dwReturn["VAKBKDARTBEZ"] = "                             ";
                    dwReturn["VAKBKDFARBNR"] = "                             ";
                    dwReturn["VAKBANDRUCK"] = " ";
                    dwReturn["VAKBMEGEW"] = "   ";
                    dwReturn["VAKBMENGEH"] = "   ";
                    dwReturn["VAKBGEWVON"] = "0";
                    dwReturn["VAKBGEWBIS"] = "0";
                    dwReturn["VAKBGEWCA"] = " ";
                    dwReturn["VAKBGEWFKT"] = "000000000000";
                    dwReturn["VAKBGEWGRP"] = " ";
                    dwReturn["VAKBMEGFKT"] = "000000000000";
                    dwReturn["VAKBMEGGRP"] = " ";
                    dwReturn["VAKBAENDZL"] = "0";
                    dwReturn["VAKBAENDBEN"] = "        ";
                    dwReturn["VAKBAENDDAT"] = "000000";
                    dwReturn["VAKBNEUBEN"] = "     2  ";
                    dwReturn["VAKBNEUDAT"] = "0";
                    dwReturn["VAKBFILLER"] = "                                          ";
                    #endregion
                    break;
            }
            INTEXDBC.MapData(true, ref dwReturn, dwInput);
        }

        public DataRow Convert2VAKDBEZ(DataRow dwInput, string ITXDBVERSION, bool IsConvert = false)
        {
            DataSet SResult = new DataSet();
            SResult = QueryVAKDBEZ(dwInput, 0);
            DataRow dwReturn = null;
            if (SResult.Tables[0].Rows.Count > 0)
            {
                dwReturn = SResult.Tables[0].Rows[0];
            }
            else
            {
                dwReturn = SResult.Tables[0].NewRow();
            }
            switch (ITXDBVERSION)
            {
                case "YGR":
                    #region=========
                    dwReturn["VAKBMANDANT"] = app.Mandant;
                    dwReturn["VAKBKOLL"] = "     ";
                    dwReturn["VAKBART"] = "                        ";
                    dwReturn["VAKBKUNDNR"] = "00000000";
                    dwReturn["VAKBFARBE"] = "          ";
                    dwReturn["VAKBKDARTNR"] = "                             ";
                    dwReturn["VAKBKDARTBEZ"] = "                             ";
                    dwReturn["VAKBKDFARBNR"] = "                             ";
                    dwReturn["VAKBANDRUCK"] = " ";
                    dwReturn["VAKBMEGEW"] = "   ";
                    dwReturn["VAKBMENGEH"] = "   ";
                    dwReturn["VAKBGEWVON"] = "0";
                    dwReturn["VAKBGEWBIS"] = "0";
                    dwReturn["VAKBGEWCA"] = " ";
                    dwReturn["VAKBGEWFKT"] = "000000000000";
                    dwReturn["VAKBGEWGRP"] = " ";
                    dwReturn["VAKBMEGFKT"] = "000000000000";
                    dwReturn["VAKBMEGGRP"] = " ";
                    dwReturn["VAKBAENDZL"] = "0";
                    dwReturn["VAKBAENDBEN"] = "        ";
                    dwReturn["VAKBAENDDAT"] = "000000";
                    dwReturn["VAKBNEUBEN"] = "     2  ";
                    dwReturn["VAKBNEUDAT"] = "0";
                    dwReturn["VAKBFILLER"] = "                                          ";
                    #endregion
                    break;
                case "JLT":

                    break;
                default:

                    break;
            }
            if (IsConvert)
            {
                foreach (DataColumn cm in dwInput.Table.Columns)
                {
                    if (dwInput[cm.ColumnName].ToString() == "")
                        continue;
                    if (dwReturn.Table.Columns.Contains(cm.ColumnName))
                    {
                        dwReturn[cm.ColumnName] = dwInput[cm.ColumnName];
                    }
                }
            }
            return dwReturn;
        }

        public void MapVAKDBEZ(ref DataRow dwLocal, DataRow dwData)
        {
            BlindMap(ref dwLocal, dwData);
        }

        public void SaveVAKDBEZ(DataRow dwData, string ITXDBVERSION = "50.24")
        {
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            setResult = QueryVAKDBEZ(dwData);
            if (setResult.Tables.Count > 0)
            {
                tbLocal = setResult.Tables[0];
                if (tbLocal.Rows.Count > 0)
                {
                    flgExist = true;
                    dwLocal = tbLocal.Rows[0];
                }
                else
                {
                    dwLocal = tbLocal.NewRow();
                }
            }
            MapVAKDBEZ(ref dwLocal, dwData);

            if (!flgExist)
            {

                InsertVAKDBEZ(dwLocal, ITXDBVERSION);
            }
            UpdateVAKDBEZ(dwLocal);
        }

        public void InsertVAKDBEZ(DataRow dwData, string ITXDBVERSION = "50.24")
        {
             or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                case "YGR":
                case "AFS":
                case "TST":
                case "UBT":
                case "SXN":
                default:
                    or1.CommandText = "INSERT INTO VAKDBEZ ( VAKBMANDANT,VAKBKOLL,VAKBART,VAKBKUNDNR,VAKBFARBE,VAKBKDARTNR,VAKBKDARTBEZ,VAKBKDFARBNR,VAKBANDRUCK,VAKBMEGEW,VAKBMENGEH,VAKBGEWVON,VAKBGEWBIS,VAKBGEWCA,VAKBGEWFKT,VAKBGEWGRP,VAKBMEGFKT,VAKBMEGGRP,VAKBAENDZL,VAKBAENDBEN,VAKBAENDDAT,VAKBNEUBEN,VAKBNEUDAT,VAKBFILLER)"
                    + " VALUES (:VAKBMANDANT:,:VAKBKOLL:,:VAKBART:,:VAKBKUNDNR:,:VAKBFARBE:,:VAKBKDARTNR:,:VAKBKDARTBEZ:,:VAKBKDFARBNR:,:VAKBANDRUCK:,:VAKBMEGEW:,:VAKBMENGEH:,:VAKBGEWVON:,:VAKBGEWBIS:,:VAKBGEWCA:,:VAKBGEWFKT:,:VAKBGEWGRP:,:VAKBMEGFKT:,:VAKBMEGGRP:,:VAKBAENDZL:,:VAKBAENDBEN:,:VAKBAENDDAT:,:VAKBNEUBEN:,:VAKBNEUDAT:,:VAKBFILLER:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public override string BATCHINSERT(DataRow dwData)
        {
            string value = "";
            switch (app.company)
            {
                case "YGR":
                case "AFS":
                case "TST":
                case "UBT":
                case "SXN":
                default:
                    value = "INTO VAKDBEZ ( VAKBMANDANT,VAKBKOLL,VAKBART,VAKBKUNDNR,VAKBFARBE,VAKBKDARTNR,VAKBKDARTBEZ,VAKBKDFARBNR,VAKBANDRUCK,VAKBMEGEW,VAKBMENGEH,VAKBGEWVON,VAKBGEWBIS,VAKBGEWCA,VAKBGEWFKT,VAKBGEWGRP,VAKBMEGFKT,VAKBMEGGRP,VAKBAENDZL,VAKBAENDBEN,VAKBAENDDAT,VAKBNEUBEN,VAKBNEUDAT,VAKBFILLER)"
                    + " VALUES (:VAKBMANDANT:,:VAKBKOLL:,:VAKBART:,:VAKBKUNDNR:,:VAKBFARBE:,:VAKBKDARTNR:,:VAKBKDARTBEZ:,:VAKBKDFARBNR:,:VAKBANDRUCK:,:VAKBMEGEW:,:VAKBMENGEH:,:VAKBGEWVON:,:VAKBGEWBIS:,:VAKBGEWCA:,:VAKBGEWFKT:,:VAKBGEWGRP:,:VAKBMEGFKT:,:VAKBMEGGRP:,:VAKBAENDZL:,:VAKBAENDBEN:,:VAKBAENDDAT:,:VAKBNEUBEN:,:VAKBNEUDAT:,:VAKBFILLER:)";
                    value = INTEXDBC.InjectData(value, dwData);
                    break;
            }
            return value;
        }

        public DataSet QueryVAKDBEZ(DataRow dwData, int option = 1)
        {

          
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 0: SResult = QVAK00(dwData); break;
                case 1: SResult = QVAK01(dwData); break;
                case 2: SResult = QVAK02(dwData); break;
                case 5: SResult = QVAK05(dwData); break;
                case 6: SResult = QVAK06(dwData); break;
                case -1: SResult = QVAK_1(dwData); break;
                case -2: SResult = QVAK_2(dwData); break;
                case 99: SResult = QVAK99(dwData); break;   
            } 
            return SResult;
        }
        public DataSet QVAK00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAKDBEZ WHERE  ROWNUM<2 "; 
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAKDBEZ  WHERE  VAKBMANDANT=:VAKBMANDANT:"
                      + " AND VAKBKOLL=:VAKBKOLL: AND VAKBART =:VAKBART: AND VAKBFARBE=:VAKBFARBE: AND VAKBKUNDNR=:VAKBKUNDNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAKDBEZ  WHERE  VAKBMANDANT=:VAKBMANDANT:"
                         + " AND VAKBKOLL=:VAKBKOLL: AND VAKBART =:VAKBART: AND VAKBFARBE=:VAKBFARBE: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK05(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*,VAKBAENDBEN,DECODE(lpad(TRIM(VAKBAENDDAT),6,'0'),'0','0','000000','000000',TO_CHAR(TO_DATE(VAKBAENDDAT,'DDMMYY'),'YYYYMMDD')) AS KBAENDDAT, VAKBNEUBEN, VAFNEUBEN,VAFNEUDAT,DECODE(lpad(TRIM(VAKBNEUDAT),6,'0'),'0','0','000000','000000',TO_CHAR(TO_DATE(VAKBNEUDAT,'DDMMYY'),'YYYYMMDD')) AS KBNEUDAT,VAFFARBGRP,VAFBEZ,VAKBKDARTBEZ"
                         + ",MAX(RTRIM(decode(GSMPOS,'01',GSMRSPZ,''))) AS VAAGESBEZ1,NVL(MAX(RTRIM(decode(GSMPOS,'01',GSMRANT,0))),0) AS VAAGESANT1"
                         + ",MAX(RTRIM(decode(GSMPOS,'02',GSMRSPZ,''))) AS VAAGESBEZ2,NVL(MAX(RTRIM(decode(GSMPOS,'02',GSMRANT,0))),0) AS VAAGESANT2"
                         + ",MAX(RTRIM(decode(GSMPOS,'03',GSMRSPZ,''))) AS VAAGESBEZ3,NVL(MAX(RTRIM(decode(GSMPOS,'03',GSMRANT,0))),0) AS VAAGESANT3"
                         + ",MAX(RTRIM(decode(GSMPOS,'04',GSMRSPZ,''))) AS VAAGESBEZ4,NVL(MAX(RTRIM(decode(GSMPOS,'04',GSMRANT,0))),0) AS VAAGESANT4"
                         + ",MAX(RTRIM(decode(GSMPOS,'05',GSMRSPZ,''))) AS VAAGESBEZ5,NVL(MAX(RTRIM(decode(GSMPOS,'05',GSMRANT,0))),0) AS VAAGESANT5"
                         + ",MAX(RTRIM(decode(GSMPOS,'06',GSMRSPZ,''))) AS VAAGESBEZ6,NVL(MAX(RTRIM(decode(GSMPOS,'06',GSMRANT,0))),0) AS VAAGESANT6"
                         + ",MAX(RTRIM(decode(GSMPOS,'07',GSMRSPZ,''))) AS VAAGESBEZ7,NVL(MAX(RTRIM(decode(GSMPOS,'07',GSMRANT,0))),0) AS VAAGESANT7"
                         + ",MAX(RTRIM(decode(GSMPOS,'08',GSMRSPZ,''))) AS VAAGESBEZ8,NVL(MAX(RTRIM(decode(GSMPOS,'08',GSMRANT,0))),0) AS VAAGESANT8"
                         + " ,NVL(SUBSTR(SDDATEN,42,3),'0') AS CR,NVL(SUBSTR(SDDATEN,45,3),'0') AS CG,NVL(SUBSTR(SDDATEN,48,3),0) AS CB,SUBSTR(SDDATEN,51,1) AS CGRP,VAKBKUNDNR AS KUNDNR,KDKUNDNA,KDNAME1, KDKNAME,NVL(SUBSTR(SDKEY,3,1),'N') AS STA "
                         + " FROM VAKDBEZ M LEFT JOIN VAFARBEN F ON VAFMANDANT=VAKBMANDANT AND VAKBKOLL=VAFKOLL AND  VAKBART=VAFART AND VAKBFARBE=VAFFARBE  "
                         + " JOIN KDSTAMM ON KDMANDANT=M.VAKBMANDANT AND VAKBKUNDNR=KDKUNDNR "
                         + " LEFT JOIN SDDAT ON SDSA='0014' AND SUBSTR(SDKEY,3,10)=VAKBFARBE AND SDKEY LIKE '%V'"
                         + " LEFT JOIN VAART ON VAAMANDANT=VAKBMANDANT AND VAAKOLL=VAKBKOLL AND VAAART=VAKBART"
                         + " LEFT JOIN GSMATK ON GSMMANDANT=VAKBMANDANT AND TRIM(GSMART)=TRIM(SUBSTR(VAKBFARBE,1,10))"
                         + " WHERE  VAKBMANDANT=:VAKBMANDANT:"
                         + " AND VAKBKOLL LIKE '" + dwData["VAKBKOLL"].ToString().Trim() + "%'"
                         + " AND VAKBART LIKE '" + dwData["VAKBART"].ToString().Trim() + "%'"
                         + dwData["VAKBFARBE"].ToString()
                        
                         + " AND VAKBKUNDNR LIKE '" + dwData["VAKBKUNDNR"].ToString().Trim() + "%'"
                         + " AND VAKBKDFARBNR LIKE '" + dwData["VAKBKDFARBNR"].ToString().Trim() + "%'"
                         + " AND VAKBKDARTBEZ LIKE '" + dwData["VAKBKDARTBEZ"].ToString().Trim() + "%'"
                         + " group by vakbmandant,vakbkoll,vakbart,vakbkundnr,vakbfarbe,vakbkdartnr,vakbkdartbez,vakbkdfarbnr, vakbandruck,vakbmegew,vakbmengeh,vakbgewvon,vakbgewbis,vakbgewca,vakbgewfkt,vakbgewgrp,vakbmegfkt,"
                         + " vakbmeggrp,vakbaendzl,vakbaendben,vakbaenddat,vakbneuben,vakbneudat,vakbfiller,VAKBAENDBEN,"
                         + " DECODE(lpad(TRIM(VAKBAENDDAT), 6, '0'),'0','0','000000','000000',TO_CHAR(TO_DATE(VAKBAENDDAT, 'DDMMYY'), 'YYYYMMDD')),VAKBNEUBEN,VAFNEUBEN,VAFNEUDAT,"
                         + " DECODE(lpad(TRIM(VAKBNEUDAT), 6, '0'),'0','0','000000','000000', TO_CHAR(TO_DATE(VAKBNEUDAT, 'DDMMYY'), 'YYYYMMDD')),"
                         + " VAFFARBGRP, VAFBEZ,VAKBKDARTBEZ,NVL(SUBSTR(SDDATEN, 42, 3), '0'), NVL(SUBSTR(SDDATEN, 45, 3), '0'),NVL(SUBSTR(SDDATEN, 48, 3), 0),SUBSTR(SDDATEN, 51, 1),KDKUNDNA,KDNAME1, KDKNAME,NVL(SUBSTR(SDKEY,3,1),'N') "
                         + " ORDER BY VAKBART,VAKBFARBE";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK06(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*,VAKBAENDBEN,VAKBAENDDAT AS KBAENDDAT, VAKBNEUBEN, VAFNEUBEN,VAFNEUDAT,VAKBNEUDAT AS KBNEUDAT,VAFFARBGRP,VAFBEZ,VAKBKDARTBEZ,VAAGESBEZ1,VAAGESANT1,VAAGESBEZ2,VAAGESANT2,VAAGESBEZ3,VAAGESANT3,VAAGESBEZ4,VAAGESANT4,VAAGESBEZ5,VAAGESANT5,VAAGESBEZ6,VAAGESANT6,VAAGESBEZ7,VAAGESANT7,VAAGESBEZ8,VAAGESANT8,"
                       + " NVL(SUBSTR(SDDATEN,42,3),'0') AS CR,NVL(SUBSTR(SDDATEN,45,3),'0') AS CG,NVL(SUBSTR(SDDATEN,48,3),0) AS CB,SUBSTR(SDDATEN,51,1) AS CGRP,KDKUNDNA,KDNAME1, KDKNAME"
                           + " FROM VAKDBEZ M LEFT JOIN VAFARBEN F ON VAFMANDANT=VAKBMANDANT AND VAKBKOLL=VAFKOLL AND  VAKBART=VAFART AND VAKBFARBE=VAFFARBE  "
                           + " JOIN KDSTAMM ON KDMANDANT=M.VAKBMANDANT AND VAKBKUNDNR=KDKUNDNR "
                           + " LEFT JOIN SDDAT ON SDSA='0014' AND SUBSTR(SDKEY,3,10)=VAKBFARBE AND SDKEY LIKE '%V'"
                           + " LEFT JOIN VAART ON VAAMANDANT=VAKBMANDANT AND VAAKOLL=VAKBKOLL AND VAAART=VAKBART"
                           + " WHERE  VAKBMANDANT=:VAKBMANDANT:  AND VAKBKOLL=:VAKBKOLL:  AND VAKBART =:VAKBART: AND VAKBFARBE =:VAKBFARBE: AND VAKBKUNDNR=:VAKBKUNDNR:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK07(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*,VAKBAENDBEN,VAKBAENDDAT AS KBAENDDAT, VAKBNEUBEN, VAFNEUBEN,VAFNEUDAT,VAKBNEUDAT AS KBNEUDAT,VAFFARBGRP,VAFBEZ,VAKBKDARTBEZ,VAAGESBEZ1,VAAGESANT1,VAAGESBEZ2,VAAGESANT2,VAAGESBEZ3,VAAGESANT3,VAAGESBEZ4,VAAGESANT4,VAAGESBEZ5,VAAGESANT5,VAAGESBEZ6,VAAGESANT6,VAAGESBEZ7,VAAGESANT7,VAAGESBEZ8,VAAGESANT8,"
                       + " NVL(SUBSTR(SDDATEN,42,3),'0') AS CR,NVL(SUBSTR(SDDATEN,45,3),'0') AS CG,NVL(SUBSTR(SDDATEN,48,3),0) AS CB,SUBSTR(SDDATEN,51,1) AS CGRP,KDKUNDNA,KDNAME1, KDKNAME"
                           + " FROM VAKDBEZ M LEFT JOIN VAFARBEN F ON VAFMANDANT=VAKBMANDANT AND VAKBKOLL=VAFKOLL AND  VAKBART=VAFART AND VAKBFARBE=VAFFARBE  "
                           + " LEFT JOIN KDSTAMM ON KDMANDANT=M.VAKBMANDANT AND VAKBKUNDNR=KDKUNDNR "
                           + " LEFT JOIN SDDAT ON SDSA='0014' AND SUBSTR(SDKEY,3,10)=VAKBFARBE AND SDKEY LIKE '%V'"
                           + " LEFT JOIN VAART ON VAAMANDANT=VAKBMANDANT AND VAAKOLL=VAKBKOLL AND VAAART=VAKBART"
                           + " WHERE  VAKBMANDANT=:VAKBMANDANT:  AND VAKBKOLL=:VAKBKOLL:  AND VAKBART =:VAKBART: AND VAKBFARBE =:VAKBFARBE: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK08(DataRow dwData)
        {
            or1.CommandText = "SELECT VAKBFARBE FROM VAKDBEZ  WHERE  VAKBMANDANT=:VAKBMANDANT: ORDER BY 1";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK09(DataRow dwData)
        {
            or1.CommandText = "SELECT VAKBFARBE FROM VAKDBEZ  WHERE  VAKBMANDANT=:VAKBMANDANT: ORDER BY 1";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK10(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAKDBEZ  WHERE  VAKBMANDANT=:VAKBMANDANT:"
                         + "   AND VAKBFARBE=:VAKBFARBE: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK_1(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT M.*,VAKBAENDBEN, VAKBAENDDAT, VAKBNEUBEN, VAFNEUBEN,VAFNEUDAT,VAKBNEUDAT,VAFFARBGRP,VAFBEZ,VAKBKDARTBEZ,VAAGESBEZ1,VAAGESANT1,VAAGESBEZ2,VAAGESANT2,VAAGESBEZ3,VAAGESANT3,VAAGESBEZ4,VAAGESANT4,VAAGESBEZ5,VAAGESANT5,VAAGESBEZ6,VAAGESANT6,VAAGESBEZ7,VAAGESANT7,VAAGESBEZ8,VAAGESANT8,"
                    + " NVL(SUBSTR(SDDATEN,42,3),'0') AS CR,NVL(SUBSTR(SDDATEN,45,3),'0') AS CG,NVL(SUBSTR(SDDATEN,48,3),0) AS CB,SUBSTR(SDDATEN,51,1) AS CGRP,KDKUNDNA,KDNAME1, KDKNAME"
                        + " FROM VAKDBEZ M JOIN VAFARBEN F ON VAFMANDANT=VAKBMANDANT AND VAKBKOLL=VAFKOLL AND  VAKBART=VAFART AND VAKBFARBE=VAFFARBE  "
                        + " JOIN KDSTAMM ON KDMANDANT=M.VAKBMANDANT AND VAKBKUNDNR=KDKUNDNR "
                        + " LEFT JOIN SDDAT ON SDSA='0014' AND SUBSTR(SDKEY,3,10)=VAKBFARBE AND SDKEY LIKE '%V'"
                        + " LEFT JOIN VAART ON VAAMANDANT=VAKBMANDANT AND VAAKOLL=VAKBKOLL AND VAAART=VAKBART"
                        + " WHERE  1=2";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK_2(DataRow dwData)
        {
            or1.CommandText = "SELECT 1 FROM VAKDBEZ  WHERE  VAKBMANDANT=:VAKBMANDANT: AND VAKBFARBE=:VAKBFARBE:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QVAK99(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM VAKDBEZ  WHERE ROWNUM<50";
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        } 
        public void DeleteVAKDBEZ(DataRow dwData, int option = 1)
        {
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1:
                    or1.CommandText = "DELETE FROM VAKDBEZ  "
                    + " WHERE  VAKBMANDANT=:VAKBMANDANT: AND VAKBKOLL=:VAKBKOLL: AND VAKBART =:VAKBART: AND VAKBFARBE=:VAKBFARBE: AND VAKBKUNDNR=:VAKBKUNDNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }

        public void UpdateVAKDBEZ(DataRow dwData, string ITXDBVERSION = "50.24", int option = 1)
        {
            string USERID = app.User;
            string DAT =  INTEXDBC.GetDBDATE().Substring(2,6);
            
            if (dwData != null)
            {
                dwData["VAKBAENDBEN"] = app.User;
                dwData["VAKBAENDDAT"] = INTEXDBC.GetDBDATE().Substring(6) + INTEXDBC.GetDBDATE().Substring(4,2)+INTEXDBC.GetDBDATE().Substring(2,2);
            }
            if (option == 0)
            {
                switch (app.company)
                {
                    case "YGR":
                    case "AFS":
                    case "TST":
                    case "UBT":
                    case "SXN":
                    default:
                        option = 1;
                        break;
                }
            }
            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                    
                case 1:
                    or1.CommandText = "UPDATE VAKDBEZ SET VAKBMANDANT=:VAKBMANDANT:,VAKBKOLL=:VAKBKOLL:,VAKBART=:VAKBART:,VAKBKUNDNR=:VAKBKUNDNR:,VAKBFARBE=:VAKBFARBE:,VAKBKDARTNR=:VAKBKDARTNR:,VAKBKDARTBEZ=:VAKBKDARTBEZ:,VAKBKDFARBNR=:VAKBKDFARBNR:,VAKBANDRUCK=:VAKBANDRUCK:,VAKBMEGEW=:VAKBMEGEW:,VAKBMENGEH=:VAKBMENGEH:,VAKBGEWVON=:VAKBGEWVON:,VAKBGEWBIS=:VAKBGEWBIS:,VAKBGEWCA=:VAKBGEWCA:,VAKBGEWFKT=:VAKBGEWFKT:,VAKBGEWGRP=:VAKBGEWGRP:,VAKBMEGFKT=:VAKBMEGFKT:,VAKBMEGGRP=:VAKBMEGGRP:,VAKBAENDZL=:VAKBAENDZL:,VAKBAENDBEN=:VAKBAENDBEN:,VAKBAENDDAT=:VAKBAENDDAT:,VAKBNEUBEN=:VAKBNEUBEN:,VAKBNEUDAT=:VAKBNEUDAT:,VAKBFILLER=:VAKBFILLER:"
                       + " WHERE  VAKBMANDANT=:VAKBMANDANT:  AND VAKBKOLL=:VAKBKOLL:  AND VAKBART =:VAKBART: AND VAKBFARBE =:VAKBFARBE: AND VAKBKUNDNR=:VAKBKUNDNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                case 2:
                    or1.CommandText = "UPDATE VAKDBEZ SET VAKBMANDANT=:VAKBMANDANT:,VAKBKOLL=:VAKBKOLL:,VAKBART=:VAKBART:,VAKBKUNDNR=:VAKBKUNDNR:,VAKBFARBE=:VAKBFARBE:,VAKBAENDBEN ='"+USERID+"',VAKBAENDDAT='"+DAT+"' "
                       + " WHERE  VAKBMANDANT=:VAKBMANDANT:  AND VAKBKOLL=:VAKBKOLL:  AND VAKBART =:VAKBART: AND VAKBFARBE =:VAKBFILLER: AND VAKBKUNDNR=:VAKBKUNDNR:";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                default:
                    break;
            }
            Connect2Proxy(or1);
        }
        public void InsertCOLOR(string COLOR)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO COLOR_REC "
                + " SELECT rec_seq.Nextval,'INSERT',TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24MISS'),'" + COLOR + "','" + COLOR + "', "
                + " '" + app.User + "   '||'ÐÂÈ¡ºÅ'||'  " + COLOR + "' FROM DUAL";
            //or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }
        public void DeleteCOLOR(string COLOR)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "INSERT INTO COLOR_REC "
                + " SELECT rec_seq.Nextval,'DELETE',TO_CHAR(SYSDATE,'YYYYMMDD'),TO_CHAR(SYSDATE,'HH24MISS'),'" + COLOR + "','" + COLOR + "', "
                + " '" + app.User + "   '||'É¾³ý'||'  " + COLOR + "' FROM DUAL";
            //or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }

    }
}
